USE Medicopedia;

CREATE TABLE Users (
			UserId INT IDENTITY(100,1) PRIMARY KEY,
			Name VARCHAR(50) NOT NULL,
			Email VARCHAR(50) NOT NULL,
			Password VARCHAR(20) NOT NULL,
			Dob DATETIME NOT NULL,	-- format: YYYY-MM-DD
			Gender VARCHAR(10) NOT NULL,
			Interest VARCHAR(100),
			SecQues VARCHAR(50) NOT NULL,
			SecAns VARCHAR(50) NOT NULL
		);

CREATE TABLE Doctors (
			RegNo INT PRIMARY KEY,
			Name VARCHAR(50) NOT NULL,
			Email VARCHAR(50) NOT NULL,
			Password VARCHAR(20) NOT NULL,
			Gender VARCHAR(10) NOT NULL,
			Dob DATETIME NOT NULL,
			Specialization VARCHAR(50) NOT NULL,
			Degree VARCHAR(50) NOT NULL
		);

CREATE TABLE Admins (
			AdminId INT IDENTITY(100,1) PRIMARY KEY,
			Password VARCHAR(20) NOT NULL,
			Email VARCHAR(50) NOT NULL
		);

CREATE TABLE Diseases (
			DiseaseName VARCHAR(100) PRIMARY KEY,
			Symptoms VARCHAR(50) NOT NULL
		);

CREATE TABLE MedicalQuery (
			QueryId	INT IDENTITY(100,1) PRIMARY KEY,
			UserId INT REFERENCES Users(UserId),
			Title VARCHAR(100) NOT NULL,
			Interest VARCHAR(50) NOT NULL,
			QueryStatus VARCHAR(20) NOT NULL,
			QueryBody VARCHAR(1024) NOT NULL
		);

CREATE TABLE MedicalAdvice (
			AdviceId INT IDENTITY(100,1) PRIMARY KEY,
			QueryId INT REFERENCES MedicalQuery(QueryId),
			RegNo INT REFERENCES Doctors(RegNo),
			AdviceBody VARCHAR(1024) NOT NULL,
			AdviceDate DATETIME NOT NULL,
			VoteUp INT DEFAULT 0,
			VoteDown INT DEFAULT 0,
			AdviceRank INT DEFAULT 0
		);

CREATE TABLE Votes (
			AdviceId INT REFERENCES MedicalAdvice(AdviceId),
			UserId	INT REFERENCES Users(UserId)
		);

CREATE TABLE Comments (
			CommentId INT IDENTITY(100,1) PRIMARY KEY,
			AdviceId INT REFERENCES MedicalAdvice(AdviceId),
			UserId	INT REFERENCES Users(UserId),
			CommentBody	VARCHAR(1024) NOT NULL
		);

CREATE TABLE Medicines (
			MedName VARCHAR(50) PRIMARY KEY,
			MedDetails VARCHAR(1024) NOT NULL,
			DiseaseName VARCHAR(100) REFERENCES Diseases(DiseaseName)
		);

CREATE TABLE Prescription (
			PresId INT IDENTITY(100,1) PRIMARY KEY,
			QueryId INT REFERENCES MedicalQuery(QueryId),
			UserId INT REFERENCES Users(UserId),
			PresBody VARCHAR(1024) NOT NULL,
			PresStatus VARCHAR(50)
		);